﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using Kugar.Core.BaseStruct;
using Kugar.Core.ExtMethod;
using SqlSugar;
using DbType = SqlSugar.DbType;

namespace Kugar.Tools.SQLHelper.SQLGenerator
{
    public interface ISQLBuilder
    {
        void Open();
        string[] GetAllDb();
        DbTableStructInfo[] GetTables(string dbName);
        DbProcInfo[] GetProcs(string dbName);
        DbTableStructInfo GetTableColumnsStruct(string dbName,string table);
    }

    public class MSSQLBuilder : ISQLBuilder
    {
        private SqlSugarClient _client = null;

        public MSSQLBuilder(string connStr)
        {
            _client=new SqlSugarClient(new ConnectionConfig()
            {
                DbType = DbType.SqlServer,
                ConnectionString = connStr,
                IsAutoCloseConnection = true,
                
            });
            _client.Ado.CommandTimeOut = 10;

        }


        public void Open()
        {
            _client.Open();

            if (_client.Ado.Connection.State!= ConnectionState.Open)
            {
                throw new Exception("连接未成功");
            }

            _client.Close();
        }

        public string[] GetAllDb()
        {
            var sql = "SELECT name FROM master.sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')";

            return _client.Ado.SqlQuery<string>(sql).ToList().ToArrayEx();
        }

        public DbTableStructInfo[] GetTables(string dbName)
        {

            var sql = $@"use [{dbName}]; 
                      select tbl.name as 表名,tbl.xtype as 类型,isnull(ds.value,'') as 注释 
                      from sysobjects tbl
                      LEFT JOIN
                      	(select * from sys.extended_properties where name='MS_Description' and isnull(minor_id,0)=0) ds ON ds.major_id=tbl.id 
                      where (xtype='u' or xtype='v')  order by tbl.name";

            var tables=_client.Ado.GetDataTable(sql);
            
            var lst=new List<DbTableStructInfo>();
            
            foreach (DataRow row in tables.Rows)
            {
                var info = GetTableColumnsStruct(dbName,row["表名"].ToStringEx());

                info.Type = (row["类型"].ToStringEx().Trim()=="U"?0:1);
                info.Name = row["表名"].ToStringEx();
                info.Comment=row["注释"].ToStringEx();

                //var template=new SQLServerTableGenerator(info);
            
                //var text=template.TransformText();
                

                lst.Add(info);
            }

            return lst.ToArray();
        }

        public DbProcInfo[] GetProcs(string dbName)
        {
            var sql = $@"
                        use [{dbName}];
                        SELECT
	                        t1.[name] AS [SP_name],
	                        t2.[name] AS [Parameter_name],
	                        t3.[name] AS [Type],
	                        t2.[isnullable] as [IsNullable],
	                        t2.[isoutparam] as IsOut,
	                        t2.[Length],
	                        t2.colorder AS [Param_order] 
                        FROM
	                        sysobjects t1
	                        INNER JOIN syscolumns t2 ON t1.[id] = t2.[id]
	                        INNER JOIN systypes t3 ON t2.xtype= t3.xtype
	                        INNER JOIN information_schema.routines r ON r.specific_name= t1.name 
                        WHERE
	                        routine_type = 'PROCEDURE' 
                        ORDER BY
	                        sp_name,
	                        [Param_order] ASC
	                        ";

            var tbl = _client.Ado.GetDataTable(sql);

            var procNames = tbl.AsEnumerable().Select(x => x["SP_name"].ToStringEx()).Distinct().ToArrayEx();

            var lst=new List<DbProcInfo>();

            foreach (var procName in procNames)
            {
                var proc = new DbProcInfo();

                proc.ProcName = procName;

                var parameters = tbl.AsEnumerable().Where(x => x["SP_name"].ToStringEx() == procName)
                    .Where(x=>x["Type"].ToStringEx() != "sysname")
                    .OrderBy(x=>x["Param_order"].ToInt())

                    .Select(x=>new DbProcInfo.ParameterInfo()
                    {
                        ParameterName = x["Parameter_name"].ToStringEx(),
                        Index =x["Param_order"].ToInt(),
                        TypeName = x["Type"].ToStringEx(),
                        Length = x["Length"].ToInt(),
                        Direction = x["IsOut"].ToBool()?ParameterDirection.Output:ParameterDirection.Input,
                        IsNullable = x["IsNullable"].ToBool()
                    })
                    .ToArrayEx();

                proc.Parameters = parameters;
                
                lst.Add(proc);
            }

           // _client.Deleteable<>()
            //var s=new SugarParameter("sdf",10){Size = 2, Direction = ParameterDirection.Output};

            //_client.Ado.UseStoredProcedure().SqlQuery<int>("")

            return lst.ToArrayEx();
            
        }

        public DbTableStructInfo GetTableColumnsStruct(string dbName,string table)
        {
            var sql =
                $@"use [{dbName}];
                   SELECT  CASE WHEN col.colorder = 1 THEN obj.name
                                ELSE ''
                            END AS 表名,
                            col.colorder AS 序号 ,
                            col.name AS 列名 ,
                            ISNULL(ep.[value], '') AS 列说明 ,
                            t.name AS 数据类型 ,
                            col.length AS 长度 ,
                            ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
                            CASE 
                                WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN 1
                            ELSE ''
                            END AS 自增量 ,
                            CASE 
                                WHEN EXISTS ( SELECT 1 
                                              FROM dbo.sysindexes si
                                              INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid 
                                              INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
                                              INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK' 
                                              WHERE sc.id = col.id AND sc.colid = col.colid ) 
                                    THEN 1 ELSE '' 
                            END AS 主键 , 
                            CASE 
                                WHEN col.isnullable = 1 THEN 1 
                                ELSE '' 
                            END AS 允许空 , 
                            ISNULL(comm.text, '') AS 默认值 
                    FROM dbo.syscolumns col 
                    LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype 
                    inner JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0
                    LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
                    LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description'
                    LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id AND epTwo.minor_id = 0 AND epTwo.name = 'MS_Description'
                    WHERE obj.name = '{table}'    --表名
                    ORDER BY col.colorder ";

            var tbl = _client.Ado.GetDataTable(sql);

            var info=new DbTableStructInfo();

            var cols=new List<DbTableInfo_Column>();
            foreach (DataRow row in tbl.Rows)
            {
                var col = new DbTableInfo_Column();
                col.Name = row["列名"].ToStringEx();
                col.Comment = row["列说明"].ToStringEx();
                col.IsNullable = ((int) row["允许空"]) == 1;
                col.IsPrimaryKey= ((int)row["主键"]) == 1;
                col.IsAutoInc = ((int)row["自增量"]) == 1;

                col.ValueType = row["数据类型"].ToStringEx();
                col.Size = (int) row["长度"].ToInt();
                col.Digit = (int) row["小数位数"].ToInt();
                
                cols.Add(col);
            }

            info.Columns = cols.ToArrayEx();

            return info;
        }


    }

    public class MySQLBuilder: ISQLBuilder
    {
        private SqlSugarClient _client = null;

        public MySQLBuilder(string connStr)
        {
            _client = new SqlSugarClient(new ConnectionConfig()
            {
                DbType = DbType.MySql,
                ConnectionString = connStr,
                IsAutoCloseConnection = true
            });

            _client.Ado.CommandTimeOut = 10;
        }

        public void Open()
        {
            _client.Open();

            if (_client.Ado.Connection.State != ConnectionState.Open)
            {
                throw new Exception("连接未成功");
            }
        }

        public DbProcInfo[] GetProcs(string dbName)
        {
            var sql = $@"
                        use {dbName};
                        SELECT PARAMETER_MODE ,PARAMETER_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_SCALE,SPECIFIC_NAME,ORDINAL_POSITION
                        FROM information_schema.parameters 
                        WHERE  SPECIFIC_SCHEMA = '{dbName}'; 
	                        ";

            var tbl = _client.Ado.GetDataTable(sql);

            var procNames = tbl.AsEnumerable().Select(x => x["SPECIFIC_NAME"].ToStringEx()).Distinct().ToArrayEx();

            var lst = new List<DbProcInfo>();

            foreach (var procName in procNames)
            {
                var proc = new DbProcInfo();

                proc.ProcName = procName;

                var parameters = tbl.AsEnumerable().Where(x => x["SPECIFIC_NAME"].ToStringEx() == procName)
                    .OrderBy(x => x["ORDINAL_POSITION"].ToInt())

                    .Select(x => new DbProcInfo.ParameterInfo()
                    {
                        ParameterName = x["PARAMETER_NAME"].ToStringEx(),
                        Index = x["ORDINAL_POSITION"].ToInt(),
                        TypeName = x["DATA_TYPE"].ToStringEx(),
                        Length = x["CHARACTER_MAXIMUM_LENGTH"].ToInt(),
                        Direction = x["PARAMETER_MODE"].ToStringEx().Switch(ParameterDirection.Input).Case("IN", ParameterDirection.Input).Case("INOUT", ParameterDirection.InputOutput).Case("OUT", ParameterDirection.Output).Result,
                        IsNullable =true
                    })
                    .ToArrayEx();

                proc.Parameters = parameters;

                lst.Add(proc);
            }

            // _client.Deleteable<>()
            //var s=new SugarParameter("sdf",10){Size = 2, Direction = ParameterDirection.Output};

            //_client.Ado.UseStoredProcedure().SqlQuery<int>("")

            return lst.ToArrayEx();

            return null;
        }


        public string[] GetAllDb()
        {
            var sql = "SELECT schema_name FROM information_schema.schemata where schema_name not in ('information_schema','mysql','performance_schema','sys','logs');";

            return _client.Ado.SqlQuery<string>(sql).ToList().ToArrayEx();
        }

        public DbTableStructInfo[] GetTables(string dbName)
        {

            var sql = $@"use {dbName}; 
                      SELECT Table_Name,Table_Comment FROM information_schema.tables where table_schema='{dbName}' and table_type='BASE TABLE';";

            DataTable tables = null;

            try
            {
                tables = _client.Ado.GetDataTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show($"获取表错误:{e.Message}");

                return new DbTableStructInfo[0];
            }

            var lst = new List<DbTableStructInfo>();

            foreach (DataRow row in tables.Rows)
            {
                var info = GetTableColumnsStruct(dbName, row["Table_Name"].ToStringEx());

                info.Type = 0;// (row["类型"].ToStringEx().Trim() == "U" ? 0 : 1);
                info.Name = row["Table_Name"].ToStringEx();
                info.Comment = row["Table_Comment"].ToStringEx();
                
                lst.Add(info);
            }

            return lst.ToArray();
        }

        public DbTableStructInfo GetTableColumnsStruct(string dbName, string table)
        {
            var sql =
                $@"use {dbName};
                   SELECT COLUMN_NAME,Column_Comment,Is_Nullable,Column_Key,EXTRA,Data_Type,CHARACTER_MAXIMUM_LENGTH,NUMERIC_SCALE,NUMERIC_PRECISION
                    FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_SCHEMA = '{dbName}' AND TABLE_NAME ='{table}' order by ordinal_position;";

            var tbl = _client.Ado.GetDataTable(sql);

            var info = new DbTableStructInfo();

            var cols = new List<DbTableInfo_Column>();
            foreach (DataRow row in tbl.Rows)
            {
                var col = new DbTableInfo_Column();
                col.Name = row["COLUMN_NAME"].ToStringEx();
                col.Comment = row["Column_Comment"].ToStringEx();
                col.IsNullable = ((string)row["Is_Nullable"]) == "YES";
                col.IsPrimaryKey = ((string)row["Column_Key"]) == "PRI";
                col.IsAutoInc = ((string)row["EXTRA"]).Contains("auto_increment",true);

                col.ValueType = row["Data_Type"].ToStringEx();
                col.Size = (int)(row["CHARACTER_MAXIMUM_LENGTH"] !=DBNull.Value?(ulong)row["CHARACTER_MAXIMUM_LENGTH"] :  (row["NUMERIC_PRECISION"]==DBNull.Value?0: (ulong)row["NUMERIC_PRECISION"])  );
                col.Digit = (int)(row["NUMERIC_SCALE"] != DBNull.Value ? (ulong)row["NUMERIC_SCALE"] : 0);

                cols.Add(col);
            }

            info.Columns = cols.ToArrayEx();

            return info;
        }

    }

    public class SqliteBuilder: ISQLBuilder
    {
        private SqlSugarClient _client = null;
        private string _connStr = "";

        public SqliteBuilder(string connStr)
        {
            _client = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = connStr,
                DbType = DbType.Sqlite,
                IsAutoCloseConnection = true
            });
            _connStr = connStr;
        }


        public void Open()
        {
            try
            {
                _client.Open();

                if (_client.Ado.Connection.State != ConnectionState.Open)
                {
                    throw new Exception("连接未成功");
                }
            }
            catch (Exception e)
            {
                throw new Exception("连接未成功");
            }

        }

        public string[] GetAllDb()
        {
            return new string[]
            {
                "Main"
            };
        }

        public DbTableStructInfo[] GetTables(string dbName)
        {
            var tables = _client.Ado.GetDataTable("SELECT * FROM sqlite_master where type ='table' AND name NOT LIKE 'sqlite_%'");

            var tableNames = tables.AsEnumerable().Select(x => x.GetString("name")).ToArrayEx();

            return tableNames.Select(x => new DbTableStructInfo()
            {
                Name = x,
                Type = 0,
                Columns = GetTableColumnsStruct(dbName,x).Columns
            }).ToArrayEx();
        }

        public DbProcInfo[] GetProcs(string dbName)
        {
            return Array.Empty<DbProcInfo>();
        }

        private Regex _colTypeRegex=new Regex("text\\s*\\(([0-9]*)\\)", RegexOptions.Compiled & RegexOptions.IgnoreCase);
        private Regex _incColRegex=new Regex("\\\"(.*)\\\".*AUTOINCREMENT",RegexOptions.IgnoreCase & RegexOptions.Multiline & RegexOptions.Compiled);
        public DbTableStructInfo GetTableColumnsStruct(string dbName, string table)
        {
            var columnsTbl = _client.Ado.GetDataTable($"PRAGMA table_info({table})");

            var t = _client.Ado.GetDataTable($"SELECT sql FROM sqlite_master where type ='table' AND name='{table}'");
                var tblSql=t.Rows[0][0].ToStringEx();

            var lst=new List<DbTableInfo_Column>(columnsTbl.Rows.Count);

            var incColNames = new HashSet<string>(StringComparer.InvariantCultureIgnoreCase);

            foreach (Match match in _incColRegex.Matches(tblSql))
            {
                incColNames.Add(match.Groups[1].Value);
            }

            foreach (DataRow row in columnsTbl.Rows)
            {
                var tmp=new DbTableInfo_Column();

                var type = row["type"] as string;
                var name = row["name"].ToStringEx();
                if (type.CompareTo("text",true))
                {
                    tmp.ValueType = "TEXT";
                    tmp.Digit = 0;
                }
                else if (type.StartsWith("text",StringComparison.InvariantCultureIgnoreCase))
                {
                    tmp.ValueType = "TEXT";
                    //tmp.Digit = _colTypeRegex.Match(type).Groups[0].Value.ToInt();
                    tmp.Size= _colTypeRegex.Match(type).Groups[1].Value.ToInt();
                }
                else
                {
                    tmp.ValueType = type;
                }

                tmp.Name = row["name"].ToStringEx();
                tmp.IsAutoInc = incColNames.Contains(name);
                tmp.IsNullable = (row["notnull"].ToStringEx().ToInt()) == 0;
                tmp.IsPrimaryKey= (row["pk"].ToStringEx().ToInt()) == 1;
                
                lst.Add(tmp);
            }

            return new DbTableStructInfo()
            {
                Columns = lst.ToArray(),
                Name = table
            };
        }
    }
}
